/**
 * 
 */
package net.kazed.nextaction.database;

import java.util.HashMap;
import java.util.Map;

import net.kazed.nextaction.database.GtdContext.Column;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String TABLE_TASK = "task";
    public static final String TABLE_TASK_CONTACT = "task_contact";
    public static final String TABLE_TASK_ATTACHMENT = "task_attachment";
    public static final String TABLE_CONTEXT = "context";
    
    private static final String TAG = "DatabaseHelper";
    private static final int DATABASE_VERSION = 20;
    
    public static final Map<String, String> CONTEXT_LIST_PROJECT_MAP;
    public static final Map<String, String> TASK_LIST_PROJECT_MAP;
    public static final Map<String, String> TASK_ATTACHMENT_PROJECT_MAP;
    
    public static final TaskContextTable TASK_CONTEXT_TABLE = new TaskContextTable();
    public static final ContextTable CONTEXT_TABLE = new ContextTable();


    /**
     * Constructor.
     * @param context Android context.
     */
    public DatabaseHelper(Context context) {
        super(context, GtdProvider.DATABASE_NAME, null, DatabaseHelper.DATABASE_VERSION);
    }
	
    @Override
    public void onCreate(SQLiteDatabase db) {
    	Log.i(TAG, "Creating nextaction DB");
        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_CONTEXT + " ("
        		+ "_id INTEGER PRIMARY KEY,"
                + "name TEXT," 
                + "parentContextId INTEGER,"
                + GtdContext.Column.NUMBER_OF_TASKS + " INTEGER,"
                + "colour INTEGER," 
                + "iconId INTEGER" + ");");

        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK + " (" 
        		+ "_id INTEGER PRIMARY KEY,"
                + "description TEXT," 
                + "details TEXT," 
                + "contextId INTEGER,"
                + "parentTaskId INTEGER,"
                + "created INTEGER,"
                + "modified INTEGER,"
                + "startDate INTEGER,"
                + "dueDate INTEGER,"
                + "displayOrder INTEGER,"
                + "complete INTEGER, "
                + Task.Column.TYPE + " INTEGER, "
                + Task.Column.STATUS + " INTEGER, "
                + Task.Column.NUMBER_OF_SUB_TASKS + " INTEGER, " 
                + Task.Column.NUMBER_OF_ALL_SUB_TASKS + " INTEGER" 
                + ");");
        
        db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK_CONTACT + " (" 
        		+ "_id INTEGER PRIMARY KEY,"
        		+ "taskId INTEGER,"
                + "contactId INTEGER" 
                + ");");

        db.execSQL("CREATE TABLE " + TaskContextTable.TABLE + " (" 
              + "_id INTEGER PRIMARY KEY,"
              + "taskId INTEGER,"
              + "contextId INTEGER" 
              + ");");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "onUpgrade: " + oldVersion + " - " + newVersion);
        switch (oldVersion) {
        case 10:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.TYPE + " INTEGER");
        case 11:
            db.execSQL("update " + DatabaseHelper.TABLE_TASK + " set " + Task.Column.TYPE + "=" + TaskStatus.UNPROCESSED.getType()
                            + " where " + Task.Column.TYPE + "= 3");
        case 12:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.STATUS + " INTEGER");
            db.execSQL("update " + DatabaseHelper.TABLE_TASK + " set " + Task.Column.STATUS + "=" + Task.Column.TYPE);
        case 13:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_CONTEXT + " ADD COLUMN " + GtdContext.Column.NUMBER_OF_TASKS + " INTEGER");
        case 16:
            db.execSQL("CREATE TABLE " + DatabaseHelper.TABLE_TASK_ATTACHMENT + " (" 
                            + "_id INTEGER PRIMARY KEY, "
                            + "taskId INTEGER, "
                            + "type TEXT, "
                            + "url TEXT"
                            + ");");
        case 17:
            try {
                db.execSQL("DROP TABLE " + DatabaseHelper.TABLE_TASK_ATTACHMENT + ";");
            } catch (Exception e) {
            }
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.NUMBER_OF_SUB_TASKS + " INTEGER");
        case 18:
            db.execSQL("ALTER TABLE " + DatabaseHelper.TABLE_TASK + " ADD COLUMN " + Task.Column.NUMBER_OF_ALL_SUB_TASKS + " INTEGER");
        case 19:
            db.execSQL("CREATE TABLE " + TaskContextTable.TABLE + " ("
                 + "_id INTEGER PRIMARY KEY,"
                 + "taskId INTEGER,"
                 + "contextId INTEGER"
                 + ");");
            initializeTaskContexts(db);
        }
        updateNumberOfTasksInAllContexts(db);
        updateNumberOfSubTasksInAllTasks(db);
    }
    
    private void initializeTaskContexts(SQLiteDatabase db) {
       SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
       qb.setTables(DatabaseHelper.TABLE_TASK);
       qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
       Cursor taskCursor = null;
       try {
           taskCursor = qb.query(db, new String[] {Task.Column.ID, Task.Column.CONTEXT_ID},
                 Task.Column.CONTEXT_ID + " is not null", null, null, null, null);
           for (taskCursor.moveToFirst(); !taskCursor.isAfterLast(); taskCursor.moveToNext()) {
               int taskId = taskCursor.getInt(0);
               int contextId = taskCursor.getInt(1);
               ContentValues values = new ContentValues();
               values.put(TaskContextTable.COLUMN_TASK_ID, taskId);
               values.put(TaskContextTable.COLUMN_CONTEXT_ID, contextId);
               db.insert(TaskContextTable.TABLE, TaskContextTable.COLUMN_TASK_ID, values);
           }
       } finally {
           if (taskCursor != null) {
               taskCursor.close();
           }
       }

    }
    
    /**
     * Update the number of tasks count of a context.
     * @param db Database.
     * @param contextId ID of context.
     */
    public void updateContextNumberOfTasksByContext(SQLiteDatabase db, int contextId) {
        Cursor cursor = null;
        try {
           final String whereClause = TaskContextTable.TABLE + "." + TaskContextTable.COLUMN_CONTEXT_ID + "=?";
           final String[] whereArg = new String[] {Integer.toString(contextId)};
           cursor = DatabaseHelper.TASK_CONTEXT_TABLE.query(db, DatabaseHelper.TASK_CONTEXT_TABLE.getColumnNames(), whereClause, whereArg, null);
           Uri uri = ContentUris.withAppendedId(GtdContext.CONTENT_ITEM_URI, contextId);
           int numberOfTasks = cursor.getCount();
           ContentValues values = new ContentValues();
           values.put(ContextTable.COLUMN_NUMBER_OF_TASKS, numberOfTasks);
           DatabaseHelper.CONTEXT_TABLE.update(db, uri, values);
        } finally {
           cursor.close();
        }
    }
    
    public void updateNumberOfTasksInAllContexts(SQLiteDatabase db) {
       Cursor cursor = null;
       try {
          cursor = DatabaseHelper.CONTEXT_TABLE.query(db, CONTEXT_TABLE.getColumnNames(), null, null, null);
          if(cursor.moveToFirst()) {
             while(!cursor.isAfterLast()) {
                int contextId = DatabaseHelper.CONTEXT_TABLE.getInteger(cursor, ContextTable.COLUMN_ID);
                updateContextNumberOfTasksByContext(db, contextId);
                cursor.moveToNext();
             }
          }
       } finally {
          if (cursor != null) {
             cursor.close();
          }
       }
    }

//    
//    /**
//     * Update the numberOfTasks field in all contexts.
//     * @param db Database.
//     */
//    public void updateNumberOfTasksInAllContexts(SQLiteDatabase db) {
//        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
//        qb.setTables(DatabaseHelper.TABLE_CONTEXT);
//        qb.setProjectionMap(CONTEXT_LIST_PROJECT_MAP);
//        Cursor contextCursor = null;
//        Cursor taskCursor = null;
//        try {
//            contextCursor = qb.query(db, new String[] {GtdContext.ID}, null, null, null, null, null);
//            for (contextCursor.moveToFirst(); !contextCursor.isAfterLast(); contextCursor.moveToNext()) {
//                int contextId = contextCursor.getInt(0);
//                SQLiteQueryBuilder taskQuery = new SQLiteQueryBuilder();
//                taskQuery.setTables(DatabaseHelper.TABLE_TASK);
//                taskQuery.setProjectionMap(TASK_LIST_PROJECT_MAP);
//                taskCursor = taskQuery.query(db, new String[] {Task.Column.ID}, Task.Column.CONTEXT_ID + " = ?",
//                                new String[] {Integer.toString(contextId)}, null, null, null);
//                int numberOfTasks = 0;
//                if (taskCursor.moveToFirst()) {
//                    numberOfTasks = taskCursor.getCount();
//                }
//                ContentValues values = new ContentValues();
//                values.put(GtdContext.Column.NUMBER_OF_TASKS, numberOfTasks);
//                db.update(DatabaseHelper.TABLE_CONTEXT, values, GtdContext.ID + " = ?", new String[] {Integer.toString(contextId)});
//                taskCursor.close();
//                taskCursor = null;
//            }
//        } finally {
//            if (contextCursor != null) {
//                contextCursor.close();
//            }
//            if (taskCursor != null) {
//                taskCursor.close();
//            }
//        }
//    }

    /**
     * Update the numberOfSubTasks field in all tasks.
     * @param db Database.
     */
    public void updateNumberOfSubTasksInAllTasks(SQLiteDatabase db) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor parentTaskCursor = null;
        try {
            Map<Integer, Integer> visitedTasks = new HashMap<Integer, Integer>();
            parentTaskCursor = qb.query(db, new String[] {Task.Column.ID}, Task.Column.PARENT_TASK_ID + " is null", null, null, null, null);
            for (parentTaskCursor.moveToFirst(); !parentTaskCursor.isAfterLast(); parentTaskCursor.moveToNext()) {
                int taskId = parentTaskCursor.getInt(0);
                updateNumberOfSubTasksOfTask(db, taskId, visitedTasks);
            }
        } finally {
            if (parentTaskCursor != null) {
                parentTaskCursor.close();
            }
        }
    }

    /**
     * Update the numberOfSubTasks field in all tasks.
     * @param db Database.
     * @param taskId Task ID.
     * @return Total number of sub tasks (recursively).
     */
    public int updateNumberOfSubTasksOfTask(SQLiteDatabase db, int taskId,Map<Integer, Integer> visitedTasks) {
        int totalSubTasks = 0;
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor subTaskCursor = null;
        try {
            subTaskCursor = qb.query(db, new String[] {Task.Column.ID}, Task.Column.PARENT_TASK_ID + " = ?",
                            new String[] {Integer.toString(taskId)}, null, null, null);
            int directSubTasks = 0;
            for (subTaskCursor.moveToFirst(); !subTaskCursor.isAfterLast(); subTaskCursor.moveToNext()) {
                int subTaskId = subTaskCursor.getInt(0);
                directSubTasks++;
                totalSubTasks++;
                if (visitedTasks.containsKey(subTaskId)) {
                    totalSubTasks += visitedTasks.get(subTaskId);
                } else {
                    totalSubTasks += updateNumberOfSubTasksOfTask(db, subTaskId, visitedTasks);
                }
            }
            ContentValues values = new ContentValues();
            values.put(Task.Column.NUMBER_OF_SUB_TASKS, directSubTasks);
            values.put(Task.Column.NUMBER_OF_ALL_SUB_TASKS, directSubTasks);
            db.update(DatabaseHelper.TABLE_TASK, values, Task.Column.ID + " = ?", new String[] {Integer.toString(taskId)});
            visitedTasks.put(taskId, totalSubTasks);
        } finally {
            if (subTaskCursor != null) {
                subTaskCursor.close();
            }
        }
        return totalSubTasks;
    }

    /**
     * Get the total number of sub tasks of task with taskId.
     * @param db Database.
     * @param taskId Task ID.
     */
    public int getTotalNumberOfSubTasksOfTask(SQLiteDatabase db, int taskId) {
        int totalSubTasks = 0;
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(DatabaseHelper.TABLE_TASK);
        qb.setProjectionMap(TASK_LIST_PROJECT_MAP);
        Cursor taskCursor = null;
        try {
            taskCursor = qb.query(db, new String[] {Task.Column.NUMBER_OF_ALL_SUB_TASKS},
                            Task.Column.PARENT_TASK_ID + " = ?",
                            new String[] {Integer.toString(taskId)}, null, null, null);
            if (taskCursor.moveToFirst()) {
                totalSubTasks = taskCursor.getInt(0);
            }
        } finally {
            if (taskCursor != null) {
                taskCursor.close();
            }
        }
        return totalSubTasks;
    }

    /**
     * Update the number of tasks count of contexts.
     * @param taskId ID of task.
    * @param db Database.
    * @param contextId TODO
     */
    int deleteTaskContextByContext(SQLiteDatabase db, int contextId) {
       return db.delete(TaskContextTable.TABLE, TaskContextTable.COLUMN_CONTEXT_ID + " = ?",
             new String[] {Integer.toString(contextId)});
    }

   static {
        CONTEXT_LIST_PROJECT_MAP = new HashMap<String, String>();
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.ID, DatabaseHelper.TABLE_CONTEXT + "._id");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.NAME, DatabaseHelper.TABLE_CONTEXT + ".name");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.COLOUR, DatabaseHelper.TABLE_CONTEXT + ".colour");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.Column.NUMBER_OF_TASKS, DatabaseHelper.TABLE_CONTEXT + ".numberOfTasks");
        CONTEXT_LIST_PROJECT_MAP.put(GtdContext.ICON, DatabaseHelper.TABLE_CONTEXT + ".iconId");
        CONTEXT_LIST_PROJECT_MAP.put(Column.PARENT_CONTEXT_ID, DatabaseHelper.TABLE_CONTEXT + "." + Column.PARENT_CONTEXT_ID);
        
        TASK_LIST_PROJECT_MAP = new HashMap<String, String>();
        TASK_LIST_PROJECT_MAP.put(Task.Column.ID, DatabaseHelper.TABLE_TASK + "._id");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DESCRIPTION, DatabaseHelper.TABLE_TASK + ".description");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DETAILS, DatabaseHelper.TABLE_TASK + ".details");
        TASK_LIST_PROJECT_MAP.put(Task.Column.CONTEXT_ID, DatabaseHelper.TABLE_TASK + ".contextId");
        TASK_LIST_PROJECT_MAP.put(Task.Column.PARENT_TASK_ID, DatabaseHelper.TABLE_TASK + ".parentTaskId");
        TASK_LIST_PROJECT_MAP.put(Task.Column.CREATED_DATE, DatabaseHelper.TABLE_TASK + ".created");
        TASK_LIST_PROJECT_MAP.put(Task.Column.MODIFIED_DATE, DatabaseHelper.TABLE_TASK + ".modified");
        TASK_LIST_PROJECT_MAP.put(Task.Column.START_DATE, DatabaseHelper.TABLE_TASK + ".startDate");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DUE_DATE, DatabaseHelper.TABLE_TASK + ".dueDate");
        TASK_LIST_PROJECT_MAP.put(Task.Column.DISPLAY_ORDER, DatabaseHelper.TABLE_TASK + ".displayOrder");
        TASK_LIST_PROJECT_MAP.put(Task.Column.COMPLETE, DatabaseHelper.TABLE_TASK + ".complete");
        TASK_LIST_PROJECT_MAP.put(Task.Column.TYPE, DatabaseHelper.TABLE_TASK + ".type");
        TASK_LIST_PROJECT_MAP.put(Task.Column.STATUS, DatabaseHelper.TABLE_TASK + ".status");
        TASK_LIST_PROJECT_MAP.put(Task.Column.NUMBER_OF_SUB_TASKS, DatabaseHelper.TABLE_TASK + ".numberOfSubTasks");
        TASK_LIST_PROJECT_MAP.put(Task.Column.NUMBER_OF_ALL_SUB_TASKS, DatabaseHelper.TABLE_TASK + ".numberOfAllSubTasks");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_NAME, DatabaseHelper.TABLE_CONTEXT + ".name");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_COLOUR, DatabaseHelper.TABLE_CONTEXT + ".colour");
        TASK_LIST_PROJECT_MAP.put(Task.CONTEXT_ICON, DatabaseHelper.TABLE_CONTEXT + ".iconId");

        TASK_ATTACHMENT_PROJECT_MAP = new HashMap<String, String>();
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.ID, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.ID);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.TASK_ID, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.TASK_ID);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.TYPE, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.TYPE);
        TASK_ATTACHMENT_PROJECT_MAP.put(TaskAttachment.Column.URL, DatabaseHelper.TABLE_TASK_ATTACHMENT + "."
                        + TaskAttachment.Column.URL);
    }
}